package com.sec.android.app.myfiles.module.local.recent;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteFullException;
import android.net.Uri;
import android.provider.MediaStore;

import com.sec.android.app.myfiles.info.AppConstants;
import com.sec.android.app.myfiles.log.Log;
import com.sec.android.app.myfiles.module.abstraction.FileRecord;
import com.sec.android.app.myfiles.module.abstraction.FileRecord.StorageType;
import com.sec.android.app.myfiles.module.download.DownloadFileRecord;
import com.sec.android.app.myfiles.module.download.DownloadProviderImp;
import com.sec.android.app.myfiles.module.download.providerapps.AbsSupportAppProviderImp;
import com.sec.android.app.myfiles.module.download.providerapps.BluetoothProviderImp;
import com.sec.android.app.myfiles.module.download.providerapps.SimpleShareProviderImp;
import com.sec.android.app.myfiles.provider.DbTableInfo;
import com.sec.android.app.myfiles.provider.DbTableInfo.COLUMN_ID;
import com.sec.android.app.myfiles.provider.PrivateMyFilesProvider;
import com.sec.android.app.myfiles.util.chain.ChainBuilder;

import java.util.ArrayList;

public class RecentProviderImp extends DownloadProviderImp {

    private static final String TRIGGER_RECENT_DATE_UPDATER = "recent_date_updater";
    private static final String TRIGGER_RECENT_MMS_WIFIDIRECT_COLLECTOR = "recent_mms_wifidirect_collector";
    private static final String TRIGGER_RECENT_MMS_WIFIDIRECT_UPDATER = "recent_mms_wifidirect_updater";
    private static final int MAX_RECENT_COUNT = 1002;
    private static final int MAX_DELETED_RECENT_COUNT = 30;
    private static final int DB_VER_COL_ADDED_GRID_ADDITIONAL_DETAILS = 209;

    private RecentDownloadProviderImp mRecentDownloadProviderImp = null;

    public RecentProviderImp(Context context) {
        super(context);
    }

    @Override
    protected AbsSupportAppProviderImp getSupportAppProviderImp() {
        mRecentDownloadProviderImp = new RecentDownloadProviderImp(mContext, this, mDB);
        ChainBuilder<AbsSupportAppProviderImp> builder = new ChainBuilder<>().
                append(mRecentDownloadProviderImp).
                append(new SimpleShareProviderImp(mContext, this, mDB)).
                append(new BluetoothProviderImp(mContext, this, mDB));
        return builder.getInstance();
    }

    @Override
    protected void _refreshDB(Uri uri, FileRecord curRecord) {
        FileRecord record = FileRecord.createFileRecord(StorageType.Downloads, AppConstants.StoragePath.DOWNLOADS);
        if (record != null) {
            PrivateMyFilesProvider.refreshDb(mContext, record);
        }
        if (mRecentDownloadProviderImp != null) {
            mRecentDownloadProviderImp.refreshAlone(uri);
        }
    }

    @Override
    protected StorageType getStorage() {
        return StorageType.Recent;
    }

    @Override
    public String[] getCreateTriggerQuery() {
        ArrayList<String> triggerList = new ArrayList<>();
        DbTableInfo tableInfo = RecentDbTableInfo.getInstance();
        DbTableInfo downloadTableInfo = DbTableInfo.getInstance(StorageType.Downloads);
        String recentTableName = tableInfo.getTableName();

        String recentDateUpdaterTrigger = "CREATE TRIGGER IF NOT EXISTS " + TRIGGER_RECENT_DATE_UPDATER +
                " AFTER INSERT ON " + recentTableName +
                " WHEN (new." + tableInfo.getColumnName(COLUMN_ID.RECENT_DATE) + " IS NULL)" +
                " BEGIN" +
                " UPDATE " + recentTableName + " SET " + tableInfo.getColumnName(COLUMN_ID.RECENT_DATE) +
                "=new." + tableInfo.getColumnName(COLUMN_ID.DATE) + " WHERE " + tableInfo.getColumnName(COLUMN_ID.ID) +
                "=new._id;" +
                "END";
        triggerList.add(recentDateUpdaterTrigger);

        if (downloadTableInfo != null) {
            String downloadTableName = downloadTableInfo.getTableName();
            final String checkExistQuery = "((SELECT count(*) FROM " + recentTableName + " WHERE " +
                    tableInfo.getColumnName(COLUMN_ID.PATH) + " LIKE " +
                    "new." + downloadTableInfo.getColumnName(COLUMN_ID.PATH) + " AND " +
                    tableInfo.getColumnName(COLUMN_ID.NAME) + " LIKE " +
                    "new." + downloadTableInfo.getColumnName(COLUMN_ID.NAME) + ") > 0)";

            String mmsWifiDirectCollectorTrigger = "CREATE TRIGGER IF NOT EXISTS " + TRIGGER_RECENT_MMS_WIFIDIRECT_COLLECTOR +
                    " AFTER INSERT ON " + downloadTableName +
                    " WHEN ((new." + downloadTableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) + "=" +
                    DownloadFileRecord.DownloadType.DOWNLOAD_BY_MESSAGE.ordinal() +
                    ") OR (new." + downloadTableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) + "=" +
                    DownloadFileRecord.DownloadType.DOWNLOAD_BY_WIFI.ordinal() +
                    ") OR (new." + downloadTableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) + "=" +
                    DownloadFileRecord.DownloadType.DOWNLOAD_BY_MTP.ordinal() + "))" +
                    " AND NOT " + checkExistQuery +
                    " BEGIN" +
                    " INSERT INTO " + recentTableName + "(" +
                    tableInfo.getColumnName(COLUMN_ID.DOWNLOAD_ID) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.NAME) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.FILE_TYPE) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.SIZE) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.DATE) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.PATH) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.EXT) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.MIME_TYPE) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.SOURCE) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.STATUS) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) + ", " +
                    tableInfo.getColumnName(COLUMN_ID.RECENT_DATE) + ")" +
                    " VALUES (new." + tableInfo.getColumnName(COLUMN_ID.DOWNLOAD_ID) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.NAME) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.FILE_TYPE) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.SIZE) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.DATE) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.PATH) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.EXT) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.MIME_TYPE) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.SOURCE) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.STATUS) +
                    ", new." + tableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) +
                    ", strftime('%s', datetime('now')) * 1000);" +
                    "END";
            triggerList.add(mmsWifiDirectCollectorTrigger);

            String mmsWifiDirectUpdaterTrigger = "CREATE TRIGGER IF NOT EXISTS " + TRIGGER_RECENT_MMS_WIFIDIRECT_UPDATER +
                    " AFTER INSERT ON " + downloadTableName +
                    " WHEN ((new." + downloadTableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) + "=" +
                    DownloadFileRecord.DownloadType.DOWNLOAD_BY_MESSAGE.ordinal() +
                    ") OR (new." + downloadTableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) + "=" +
                    DownloadFileRecord.DownloadType.DOWNLOAD_BY_WIFI.ordinal() +
                    ") OR (new." + downloadTableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY) + "=" +
                    DownloadFileRecord.DownloadType.DOWNLOAD_BY_MTP.ordinal() + "))" +
                    " AND " + checkExistQuery +
                    " BEGIN" +
                    " UPDATE " + recentTableName +
                    " SET " + tableInfo.getColumnName(COLUMN_ID.RECENT_DATE) + "=(strftime('%s', datetime('now')) * 1000)" +
                    ", " + tableInfo.getColumnName(COLUMN_ID.SIZE) + "=new." + tableInfo.getColumnName(COLUMN_ID.SIZE) +
                    ", " + tableInfo.getColumnName(COLUMN_ID.DATE) + "=new." + tableInfo.getColumnName(COLUMN_ID.DATE) +
                    ", " + tableInfo.getColumnName(COLUMN_ID.DOWNLOADED_ITEM_VISIBILITY) + "=1" +
                    " WHERE " + tableInfo.getColumnName(COLUMN_ID.PATH) + " LIKE " +
                    "new." + downloadTableInfo.getColumnName(COLUMN_ID.PATH) + " AND " +
                    tableInfo.getColumnName(COLUMN_ID.NAME) + " LIKE " +
                    "new." + downloadTableInfo.getColumnName(COLUMN_ID.NAME) + ";" +
                    "END";
            triggerList.add(mmsWifiDirectUpdaterTrigger);
        }

        String[] ret = new String[triggerList.size()];
        return triggerList.toArray(ret);
    }

    @Override
    public String[] getDropTriggerQuery() {
        String[] ret = new String[3];
        ret[0] = "DROP TRIGGER IF EXISTS " + TRIGGER_RECENT_DATE_UPDATER;
        ret[1] = "DROP TRIGGER IF EXISTS " + TRIGGER_RECENT_MMS_WIFIDIRECT_COLLECTOR;
        ret[2] = "DROP TRIGGER IF EXISTS " + TRIGGER_RECENT_MMS_WIFIDIRECT_UPDATER;
        return ret;
    }

    @Override
    protected int _bulkInsert(Uri uri, ContentValues[] values) {
        int ret = 0;
        DbTableInfo tableInfo = DbTableInfo.getInstance(getStorage());
        ArrayList<ContentValues> contentValues = new ArrayList<>();
        ArrayList<ContentValues> updateValues = new ArrayList<>();
        if (tableInfo != null) {
            String pathCol = tableInfo.getColumnName(COLUMN_ID.PATH);
            String nameCol = tableInfo.getColumnName(COLUMN_ID.NAME);
            Log.d(this, "_bulkInsert start");

            for (ContentValues cv : values) {
                if (cv != null) {
                    String path = cv.getAsString(pathCol);
                    String name = cv.getAsString(nameCol);
                    if (isRecordExist(tableInfo, path, name)) {
                        updateValues.add(cv);
                    } else {
                        contentValues.add(cv);
                    }
                }
            }

            if (!updateValues.isEmpty()) {
                String selection = pathCol + "=? AND " + nameCol + "=?";
                for (ContentValues value : updateValues) {
                    String path = value.getAsString(pathCol);
                    String name = value.getAsString(nameCol);
                    String[] args = new String[]{path, name};
                    _update(value, selection, args);
                }
            }
            if (!contentValues.isEmpty()) {
                ContentValues[] newValues = new ContentValues[contentValues.size()];
                newValues = contentValues.toArray(newValues);
                ret = super._bulkInsert(uri, newValues);
            }
            checkExceedItem(uri);
        }
        Log.d(this, "_bulkInsert end");
        return ret;
    }

    @Override
    protected Uri _insert(Uri uri, ContentValues values) {
        Uri ret = null;
        if (values == null) {
            values = new ContentValues();
        }
        DbTableInfo tableInfo = DbTableInfo.getInstance(getStorage());
        if (tableInfo != null) {
            String pathCol = tableInfo.getColumnName(COLUMN_ID.PATH);
            String nameCol = tableInfo.getColumnName(COLUMN_ID.NAME);
            String path = values.getAsString(pathCol);
            String name = values.getAsString(nameCol);
            String selection = pathCol + "=? AND " + nameCol + "=?";
            String[] args = new String[]{path, name};

            if (isRecordExist(tableInfo, path, name)) {
                values.put(tableInfo.getColumnName(COLUMN_ID.DOWNLOADED_ITEM_VISIBILITY), 1);
                int rowId = _update(values, selection, args);
                if (rowId > 0) {
                    ret = uri;
                }
            } else {
                ret = super._insert(uri, values);
            }
        }
        checkExceedItem(uri);
        return ret;
    }

    @Override
    protected Cursor _query(Uri uri, String[] projectionIn, String selection, String[] selectionArgs, String sortOrder) {
        Cursor c = super._query(uri, projectionIn, selection, selectionArgs, sortOrder);
        if (c != null) {
            int count = c.getCount();
            if (count > MAX_RECENT_COUNT) {
                Log.d(this, "count of recent : " + count);
                c.close();
                removeExceed();
                c = super._query(uri, projectionIn, selection, selectionArgs, sortOrder);
            }
        }
        return c;
    }

    private void checkExceedItem(Uri uri) {
        String dateCol = mDbTableInfo.getColumnName(COLUMN_ID.RECENT_DATE);
        Cursor cursor = _query(uri, null, null, null, dateCol + " DESC");
        if (cursor != null) {
            int count = cursor.getCount();
            Log.d(this, "count of recent : " + count);
            if (count > MAX_RECENT_COUNT) {
                Log.d(this, "Recent exceed max limit : " + count);
                removeExceed();
            }
            cursor.close();
        }
    }

    private void removeExceed() {
        Log.d(this, "removeExceed");
        String idCol = mDbTableInfo.getColumnName(COLUMN_ID.ID);
        String dateCol = mDbTableInfo.getColumnName(COLUMN_ID.RECENT_DATE);
        String visibilityColName = mDbTableInfo.getColumnName(COLUMN_ID.DOWNLOADED_ITEM_VISIBILITY);

        try {
            StringBuilder query = new StringBuilder();
            query.append("UPDATE ").append(mDbTableInfo.getTableName())
                    .append(" SET ").append(visibilityColName).append("=0")
                    .append(" WHERE ")
                    .append(idCol)
                    .append(" NOT IN (SELECT ")
                    .append(idCol)
                    .append(" FROM ")
                    .append(mDbTableInfo.getTableName())
                    .append(" WHERE ")
                    .append(visibilityColName).append("=1")
                    .append(" ORDER BY ")
                    .append(dateCol)
                    .append(" DESC LIMIT ")
                    .append(MAX_RECENT_COUNT)
                    .append(')')
                    .append(" AND ").append(visibilityColName).append("=1");
            mDB.execSQL(query.toString());
        } catch (SQLiteFullException e) {
            Log.e(this, "SQLiteFullException:" + e.toString());
        }
    }

    private boolean isRecordExist(DbTableInfo tableInfo, String path, String name) {
        boolean ret = false;
        String pathCol = tableInfo.getColumnName(COLUMN_ID.PATH);
        String nameCol = tableInfo.getColumnName(COLUMN_ID.NAME);
        String selection = pathCol + "=? AND " + nameCol + "=?";
        String[] args = new String[]{path, name};

        Cursor cursor = mDB.query(getTableName(), null, selection, args, null, null, null);
        if (cursor != null) {
            ret = cursor.getCount() != 0;
            cursor.close();
        }
        return ret;
    }

    @Override
    protected int _delete(String selection, String[] selectionArgs) {
        int ret = super._delete(selection, selectionArgs);
        final String _selection = mDbTableInfo.getColumnName(COLUMN_ID.DOWNLOADED_ITEM_VISIBILITY) + "=0";
        Cursor c = mDB.query(getTableName(), null, _selection, null, null, null, null);
        if (c != null) {
            if (c.getCount() > MAX_DELETED_RECENT_COUNT) {
                clearDeletedRecord();
            }
            c.close();
        }
        return ret;
    }

    private void clearDeletedRecord() {
        mSupportApps.clearUnnecessaryData();
        StringBuilder selection = new StringBuilder();
        selection.append(mDbTableInfo.getColumnName(COLUMN_ID.DOWNLOAD_BY))
                .append(" IS NULL AND ")
                .append(mDbTableInfo.getColumnName(COLUMN_ID.DOWNLOADED_ITEM_VISIBILITY))
                .append("=0");
        mDB.delete(getTableName(), selection.toString(), null);
    }

    @Override
    protected void updateTable(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(this, "oldVersion " + oldVersion + " newVersion " + newVersion);
        if (oldVersion > MIN_MYFILE_2016_DB_VERSION) {
            Log.d(this, "alter table");
            try {
                db.beginTransaction();
                if (oldVersion < DB_VER_COL_ADDED_GRID_ADDITIONAL_DETAILS) {
                    db.execSQL("ALTER TABLE " + mDbTableInfo.getTableName() +
                            " ADD COLUMN " + MediaStore.Files.FileColumns.WIDTH + " integer");
                    db.execSQL("ALTER TABLE " + mDbTableInfo.getTableName() +
                            " ADD COLUMN " + MediaStore.Files.FileColumns.HEIGHT + " integer");
                    db.execSQL("ALTER TABLE " + mDbTableInfo.getTableName() +
                            " ADD COLUMN " + MediaStore.Video.VideoColumns.DURATION + " integer");
                    db.execSQL("ALTER TABLE " + mDbTableInfo.getTableName() +
                            " ADD COLUMN " + "resumePos" + " integer");
                    db.execSQL("ALTER TABLE " + mDbTableInfo.getTableName() +
                            " ADD COLUMN " + MediaStore.Audio.AlbumColumns.ARTIST + " text");
                    db.execSQL("ALTER TABLE " + mDbTableInfo.getTableName() +
                            " ADD COLUMN " + MediaStore.Audio.AlbumColumns.ALBUM + " integer");
                }
                if (!isColumnExists(db, mDbTableInfo.getTableName(), mDbTableInfo.getColumnName(COLUMN_ID.IS_360_CONTENTS))) {
                    Log.d(this, mDbTableInfo.getColumnName(COLUMN_ID.IS_360_CONTENTS)+ " column not exist");
                    db.execSQL("ALTER TABLE " + mDbTableInfo.getTableName() +
                            " ADD COLUMN " + mDbTableInfo.getColumnName(COLUMN_ID.IS_360_CONTENTS) + " integer");
                } else {
                    Log.d(this, mDbTableInfo.getColumnName(COLUMN_ID.IS_360_CONTENTS) + " column exist");
                }
                db.setTransactionSuccessful();
            } catch (SQLiteFullException e) {
                Log.e(this, "SQLiteFullException:" + e.toString());
            } catch (IllegalStateException e) {
                Log.e(this, e.getMessage());
            } finally {
                db.endTransaction();
            }
        } else {
            super.updateTable(db, oldVersion, newVersion);
        }
    }
}